/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.CustomerDTO;
import east2west.interfaces.CustomerInterface;
import east2west.service.JobService;
import east2west.util.DatabaseUtil;
import east2west.util.DateTimeUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author thanhluan
 */
public class CustomerDAO implements CustomerInterface {

    Connection con = null;
    CustomerDTO cus = null;
    CallableStatement call = null;

    public ArrayList<CustomerDTO> readByAll() {
        ArrayList<CustomerDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Customer_GetAll}");
            ResultSet rs = call.executeQuery();
            list = new ArrayList<CustomerDTO>();
            while (rs.next()) {
                cus = new CustomerDTO();
                cus.setCustomerId(rs.getInt(1));
                cus.setEmail(rs.getString(2));
                cus.setFullName(rs.getString(3));
                cus.setAddress(rs.getString(4));
                cus.setPhone(rs.getString(5));
                cus.setFax(rs.getString(6));
                cus.setJobId(rs.getInt(7));
                Date date = rs.getDate(8);
                if (date == null) {
                    cus.setBirthDay("");
                } else {
                    cus.setBirthDay(DateTimeUtil.convert2String(date, "dd-MM-yyyy"));
                }
                cus.setCountry(rs.getString(9));
                cus.setState(rs.getString(10));

                list.add(cus);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;

        }
    }

    public ArrayList<CustomerDTO> findByName(String name) {
        ArrayList<CustomerDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Customer_FindName(?)}");
            call.setString(1, name);

            ResultSet rs = call.executeQuery();
            list = new ArrayList<CustomerDTO>();
            while (rs.next()) {
                cus = new CustomerDTO();
                cus.setCustomerId(rs.getInt(1));
                cus.setEmail(rs.getString(2));
                cus.setFullName(rs.getString(3));
                cus.setAddress(rs.getString(4));
                cus.setPhone(rs.getString(5));
                cus.setFax(rs.getString(6));
                cus.setJobId(rs.getInt(7));
                Date date = rs.getDate(8);
                if (date == null) {
                    cus.setBirthDay("");
                } else {
                    cus.setBirthDay(DateTimeUtil.convert2String(date, "dd-MM-yyyy"));
                }
                cus.setCountry(rs.getString(9));
                cus.setState(rs.getString(10));

                list.add(cus);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            return null;

        }
    }

    public CustomerDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_Customers where CustId = ?");
            call.setInt(1, id);
            ResultSet rs = call.executeQuery();
            JobService serJob = new JobService();
            while (rs.next()) {
                cus = new CustomerDTO();
                cus.setCustomerId(rs.getInt(1));
                cus.setEmail(rs.getString(2));
                cus.setFullName(rs.getString(3));
                cus.setAddress(rs.getString(4));
                cus.setPhone(rs.getString(5));
                cus.setFax(rs.getString(6));
                cus.setJobDTO(serJob.findById(rs.getInt(7)));
                Date date = rs.getDate(8);
                if (date == null) {
                    cus.setBirthDay("");
                } else {
                    cus.setBirthDay(DateTimeUtil.convert2String(date, "MM/dd/yyyy"));
                }
                cus.setCountry(rs.getString(9));
                cus.setState(rs.getString(10));
            }
            rs.close();
            call.close();
            return cus;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<CustomerDTO> findByJob(int jobId) {
        ArrayList<CustomerDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Customer_FindJobId(?)}");
            call.setInt(1, jobId);
            list = new ArrayList<CustomerDTO>();
            ResultSet rs = call.executeQuery();
            while (rs.next()) {
                cus = new CustomerDTO();
                cus.setCustomerId(rs.getInt(1));
                cus.setEmail(rs.getString(2));
                cus.setFullName(rs.getString(3));
                cus.setAddress(rs.getString(4));
                cus.setPhone(rs.getString(5));
                cus.setFax(rs.getString(6));
                cus.setJobId(rs.getInt(7));
                Date date = rs.getDate(8);
                if (date == null) {
                    cus.setBirthDay("");
                } else {
                    cus.setBirthDay(DateTimeUtil.convert2String(date, "dd-MM-yyyy"));
                }
                cus.setCountry(rs.getString(9));
                cus.setState(rs.getString(10));
                list.add(cus);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            return null;
        }
    }

    public int create(CustomerDTO dto) {
        try {
            //dto = new CustomerDTO();
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("{call sp_Insert_Customers(?,?,?,?,?,?,?,?,?,?)}");
            call.setString(1, dto.getEmail());
            call.setString(2, dto.getFullName());
            call.setString(3, dto.getAddress());
            call.setString(4, dto.getPhone());
            call.setString(5, dto.getFax());
            call.setInt(6, dto.getJobId());
            // java.sql.Date date = DateTimeUtil.convert2Date(dto.getBirthDay(), "MM/dd/yyyy");
            call.setString(7, dto.getBirthDay());
            call.setString(8, dto.getCountry());
            call.setString(9, dto.getState());
            call.registerOutParameter(10, java.sql.Types.INTEGER);
            call.executeUpdate();
            int cusId = call.getInt(10);
            call.close();
            return cusId;
        } catch (SQLException ex) {
            return 0;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(CustomerDTO dto) {
        try {
            dto = new CustomerDTO();
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Update_Customers(?,?,?,?,?,?,?,?)}");
            call.setInt(1, dto.getCustomerId());
            call.setString(2, dto.getEmail());
            call.setString(3, dto.getFullName());
            call.setString(4, dto.getAddress());
            call.setString(5, dto.getPhone());
            call.setString(6, dto.getFax());
            call.setInt(7, dto.getJobId());
            call.setByte(8, dto.getIsDelete());
            call.setDate(9, DateTimeUtil.convert2Date(dto.getBirthDay(), "dd-MM-yyyy"));
            call.setString(10, dto.getCountry());
            call.setString(11, dto.getState());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {

                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Delete_Customers(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {

                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(CustomerDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public CustomerDTO findByEmail(String email) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_Customers where Email = ?");
            call.setString(1, email);
            ResultSet rs = call.executeQuery();
            JobService serJob = new JobService();
            while (rs.next()) {
                cus = new CustomerDTO();
                cus.setCustomerId(rs.getInt(1));
                cus.setEmail(rs.getString(2));
                cus.setFullName(rs.getString(3));
                cus.setAddress(rs.getString(4));
                cus.setPhone(rs.getString(5));
                cus.setFax(rs.getString(6));
                cus.setJobDTO(serJob.findById(rs.getInt(7)));
                Date date = rs.getDate(8);
                if (date == null) {
                    cus.setBirthDay("");
                } else {
                    cus.setBirthDay(DateTimeUtil.convert2String(date, "MM/dd/yyyy"));
                }
                cus.setCountry(rs.getString(9));
                cus.setState(rs.getString(10));
            }
            rs.close();
            call.close();
            return cus;
        } catch (SQLException ex) {
            return null;
        }
    }
}
